<?php

if (file_exists(APPPATH.'models/basemodel.php')) include_once(APPPATH.'models/basemodel.php');

class ActivityModel extends BaseModel {
	
	public function ActivityModel() {
		parent::BaseModel();
		$this->setModel('activity');
		$this->obj =& get_instance();
		$this->load->database();
	}
	
	public function getFullDataForActivity($activityid) {
		$sql = "SELECT A.id as activityid, A.activity_title, A.completion, J.id as jobid, J.job_title, A.companyid as companyid, CC.centertitle, A.milestone, A.deadline
				FROM activity A LEFT JOIN job_costcenter JC ON (A.job_centerid = JC.id) LEFT JOIN job J ON (A.jobid = J.id) LEFT JOIN costcenter CC ON (JC.costcenterid = CC.id)
				WHERE A.id = ".intval($activityid);
	
		$rs = $this->db->query($sql)->result();
		
		if (count($rs)) return $rs[0];
		else return false;
	}
	
	public function whatsComingUp($jobid = false, $usersid = false, $from, $to, $companyid = false) {
		$sql = "SELECT A.*, A.startdate AS dateapplicable, J.job_title, J.companyid, C.company_name
				FROM activity A LEFT JOIN assignment S ON ( (A.jobid = S.jobid AND A.jobid > 0) OR (S.job_costcenterid = A.job_centerid AND A.job_centerid > 0) )
					LEFT JOIN job J ON (A.jobid = J.id)
					LEFT JOIN company C ON (J.companyid = C.id)
				WHERE 1 = 1 AND A.deleted = 0 ";

		if ($usersid) $sql .= " AND S.userid = ".intval($usersid)." ";
	
		if ($jobid) {
			if (is_array($jobid)) $sql .= " AND A.jobid IN (".implode(',', $jobid).")";
			else $sql .= " AND A.jobid = ".intval($jobid);
		}
		
		if ($companyid) {
			if (is_array($companyid)) $sql .= " AND A.companyid IN (".implode(',', $companyid).")";
			else $sql .= " AND A.companyid = ".intval($companyid);
		}

		$sql .= " AND A.startdate <= ".$this->db->escape($to)." AND A.startdate >= ".$this->db->escape($from)." AND (A.startdate != A.enddate OR (A.startdate IS NOT NULL AND A.enddate IS NULL )) ";
		
		
		if (!$from && $to) $sql .= " AND ( A.startdate <= ".$this->db->escape($to)." OR A.enddate <= ".$this->db->escape($to).") ";
		
		$sql .=	" GROUP BY A.id ";
		
		$sql .= " UNION ";
		
		$sql .= "SELECT A.*, A.enddate as dateapplicable, J.job_title, J.companyid, C.company_name
			FROM activity A LEFT JOIN assignment S ON ( (A.jobid = S.jobid AND A.jobid > 0) OR (S.job_costcenterid = A.job_centerid AND A. job_centerid > 0) )
				LEFT JOIN job J ON (A.jobid = J.id)
				LEFT JOIN company C ON (J.companyid = C.id)
			WHERE 1 = 1 AND A.deleted = 0 ";

		if ($usersid) $sql .= " AND S.userid = ".intval($usersid)." ";
	
		if ($jobid) {
			if (is_array($jobid)) $sql .= " AND A.jobid IN (".implode(',', $jobid).")";
			else $sql .= " AND A.jobid = ".intval($jobid);
		}

		if ($companyid) {
			if (is_array($companyid)) $sql .= " AND A.companyid IN (".implode(',', $companyid).")";
			else $sql .= " AND A.companyid = ".intval($companyid);
		}

		$sql .= " AND A.enddate <= ".$this->db->escape($to)." AND A.enddate >= ".$this->db->escape($from)." ";
		
		$sql .=	" GROUP BY A.id ";
		
		
		$sql .= " ORDER BY dateapplicable ASC";

		$rs = $this->db->query($sql)->result();
		
		if (count($rs)) return $rs;
		else return false;

	}
	
	public function missedComingUp($jobid = false, $usersid = false) {
		$sql = "SELECT A.*, A.enddate AS dateapplicable, J.job_title, J.companyid, C.company_name
				FROM activity A LEFT JOIN assignment S ON ( (A.jobid = S.jobid AND A.jobid > 0) OR (S.job_costcenterid = A.job_centerid AND A. job_centerid > 0) )
					LEFT JOIN job J ON (A.jobid = J.id)
					LEFT JOIN company C ON (J.companyid = C.id)
				WHERE 1 = 1 AND A.deleted = 0 ";

		if ($usersid) $sql .= " AND S.userid = ".intval($usersid)." ";
	
		if ($jobid) {
			if (is_array($jobid)) $sql .= " AND A.jobid IN (".implode(',', $jobid).")";
			else $sql .= " AND A.jobid = ".intval($jobid);
		}

		$sql .= " AND A.enddate <= NOW()
					AND A.completion = 0";
		
		$sql .=	" GROUP BY A.id ";
		
		$rs = $this->db->query($sql)->result();
		
		if (count($rs)) return $rs;
		else return false;
	}
	
	public function markCompleted($activityid) {
		$sql = "UPDATE activity
				SET completion = 1
				WHERE id = ".intval($activityid);
	
		return $this->db->query($sql);
	}
	
	public function markNotCompleted($activityid) {
		$sql = "UPDATE activity
				SET completion = 0
				WHERE id = ".intval($activityid);
	
		return $this->db->query($sql);
	}
	
	/*
	ALTER TABLE `activity` ADD `creatorid` int(11) DEFAULT NULL ;
	ALTER TABLE `activity` ADD `updaterid` int(11) DEFAULT NULL ;
	*/
}

?>